﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace DataManager.DAL
{
    public struct Movie
    {
        int m_MovieId;
        string m_OriginalTitle;
        string m_HungarianTitle;
        int m_ReleaseDate;
        int m_Price;
        int m_Quantity;

        public int MovieId
        {
            get { return m_MovieId; }
            set { m_MovieId = value; }
        }

        public string OriginalTitle
        {
            get { return m_OriginalTitle; }
            set { m_OriginalTitle = value; }
        }

        public string HungarianTitle
        {
            get { return m_HungarianTitle; }
            set { m_HungarianTitle = value; }
        }

        public int ReleaseDate
        {
            get { return m_ReleaseDate; }
            set { m_ReleaseDate = value; }
        }

        public int Price
        {
            get { return m_Price; }
            set { m_Price = value; }
        }

        public int Quantity
        {
            get { return m_Quantity; }
            set { m_Quantity = value; }
        }
    }

    class Movies : DAL
    {
        /// <summary>
        /// Visszateriti a filmek listajat.
        /// Lehetseges szuresek: cim vagy megjelenesi ev alapjan
        /// </summary>
        /// <param name="title"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public List<Movie> GetMovieList(string title, int year)
        {
            string query = "SELECT FilmId, EredetiCim, MagyarCim, MegjEv, Ar, MennyRakt FROM Filmek ";
            if (title != "")
            {
                query = query + "WHERE EredetiCim LIKE '%" + title + "%' OR MagyarCim LIKE '%" + title + "%'";
            }
            if (year != 0)
            {
                query = query + "WHERE MegjEv = " + year;
            }
            string error = string.Empty;
            SqlDataReader rdr = ExecuteReader(query, ref error);
            List<Movie> movieList = new List<Movie>();
            if (error == "OK")
            {
                while (rdr.Read())
                {
                    Movie item = new Movie();
                    item.MovieId = Convert.ToInt32(rdr["FilmId"]);
                    item.OriginalTitle = rdr["EredetiCim"].ToString();
                    item.HungarianTitle = rdr["MagyarCim"].ToString();
                    item.ReleaseDate = Convert.ToInt32(rdr["MegjEv"]);
                    item.Price = Convert.ToInt32(rdr["Ar"]);
                    item.Quantity = Convert.ToInt32(rdr["MennyRakt"]);
                    movieList.Add(item);
                }
            }
            CloseDataReader(rdr);
            return movieList;
        }

        /// <summary>
        /// Visszateriti azokat a filmeket amikben a keresesnek megfelelo szinesz/szineszek jatszanak
        /// </summary>
        /// <param name="actorName"></param>
        /// <returns></returns>
        public List<Movie> GetMovieListOnActor(string actorName)
        {
            string query = "SELECT Filmek.FilmId, EredetiCim, MagyarCim, MegjEv, Ar, MennyRakt " +
                "FROM Filmek, Jatsza, Szineszek " +
                "WHERE Filmek.FilmId = Jatsza.FilmId " +
                "AND Jatsza.SzineszId = Szineszek.SzineszId " +
                "AND Szineszek.SzineszNev LIKE '%" + actorName + "%'";
            string error = string.Empty;
            SqlDataReader rdr = ExecuteReader(query, ref error);
            List<Movie> movieList = new List<Movie>();
            if (error == "OK")
            {
                while (rdr.Read())
                {
                    Movie item = new Movie();
                    item.MovieId = Convert.ToInt32(rdr["FilmId"]);
                    item.OriginalTitle = rdr["EredetiCim"].ToString();
                    item.HungarianTitle = rdr["MagyarCim"].ToString();
                    item.ReleaseDate = Convert.ToInt32(rdr["MegjEv"]);
                    item.Price = Convert.ToInt32(rdr["Ar"]);
                    item.Quantity = Convert.ToInt32(rdr["MennyRakt"]);
                    movieList.Add(item);
                }
            }
            CloseDataReader(rdr);
            return movieList;
        }
        
        /// <summary>
        /// Visszateriti azokat a filmeket amelyeket a keresesnek megfelelo rendezok rendeztek
        /// </summary>
        /// <param name="directorName"></param>
        /// <returns></returns>
        public List<Movie> GetMovieListOnDirector(string directorName)
        {
            string query = "SELECT DISTINCT Filmek.FilmId, EredetiCim, MagyarCim, MegjEv, Ar, MennyRakt " +
                "FROM Filmek, Rendezte, Rendezok " +
                "WHERE Filmek.FilmId = Rendezte.FilmId " +
                "AND Rendezte.RendezoId = Rendezok.RendezoId " +
                "AND Rendezok.RendezoNev LIKE '%" + directorName + "%'";
            string error = string.Empty;
            SqlDataReader rdr = ExecuteReader(query, ref error);
            List<Movie> movieList = new List<Movie>();
            if (error == "OK")
            {
                while (rdr.Read())
                {
                    Movie item = new Movie();
                    item.MovieId = Convert.ToInt32(rdr["FilmId"]);
                    item.OriginalTitle = rdr["EredetiCim"].ToString();
                    item.HungarianTitle = rdr["MagyarCim"].ToString();
                    item.ReleaseDate = Convert.ToInt32(rdr["MegjEv"]);
                    item.Price = Convert.ToInt32(rdr["Ar"]);
                    item.Quantity = Convert.ToInt32(rdr["MennyRakt"]);
                    movieList.Add(item);
                }
            }
            CloseDataReader(rdr);
            return movieList;
        }

        /// <summary>
        /// Visszateriti egy bizonyos Id-ju film cimet
        /// </summary>
        /// <param name="movieId"></param>
        /// <returns></returns>
        public string GetMovieTitle(int movieId)
        {
            string query = "SELECT EredetiCim FROM Filmek WHERE FilmId = " + movieId;
            string error = string.Empty;
            SqlDataReader rdr = ExecuteReader(query, ref error);
            string title = string.Empty;
            if (error == "OK")
            {
                while (rdr.Read())
                {
                    title = rdr["EredetiCim"].ToString();
                }
            }
            CloseDataReader(rdr);
            return title;
            
        }

        /// <summary>
        /// Beszur egy filmet, tarolt eljaras hasznalataval
        /// </summary>
        /// <param name="originalTitle"></param>
        /// <param name="hunTitle"></param>
        /// <param name="year"></param>
        /// <param name="price"></param>
        /// <param name="quantity"></param>
        /// <returns></returns>
        public string InsertMovie(string originalTitle, string hunTitle, int year, int price, int quantity)
        {
            string[] parameterNames = new string[5], parameterValues = new string[5];
            parameterNames[0] = "@EredetiCim"; parameterValues[0] = originalTitle.ToString();
            parameterNames[1] = "@MagyarCim"; parameterValues[1] = hunTitle.ToString();
            parameterNames[2] = "@MegjEv"; parameterValues[2] = year.ToString();
            parameterNames[3] = "@Ar"; parameterValues[3] = price.ToString();
            parameterNames[4] = "@MennyRakt"; parameterValues[4] = quantity.ToString();

            return ExecuteStoredProcedureNonQuery("UjFilm", parameterNames, parameterValues);
        }

        /// <summary>
        /// Modositja egy film adatait.
        /// </summary>
        /// <param name="movieId"></param>
        /// <param name="originalTitle"></param>
        /// <param name="hunTitle"></param>
        /// <param name="year"></param>
        /// <param name="price"></param>
        /// <param name="quantity"></param>
        /// <returns></returns>
        public string UpdateMovie(int movieId, string originalTitle, string hunTitle, int year, int price, int quantity)
        {
            string query = "UPDATE Filmek " +
                "SET EredetiCim = '" + originalTitle +
                "', MagyarCim = '" + hunTitle +
                "', MegjEv = " + year +
                ", Ar = " + price +
                ", MennyRakt = " + quantity +
                " WHERE FilmId = " + movieId;
            return ExecuteNonQuery(query);
        }

        /// <summary>
        /// Torol egy filmet
        /// </summary>
        /// <param name="movieId"></param>
        /// <returns></returns>
        public string DeleteMovie(int movieId)
        {
            string query = "DELETE FROM Filmek WHERE FilmId = " + movieId;
            return ExecuteNonQuery(query);
        }
    }
}
